package JurisPro.modelo.banco;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import JurisPro.modelo.FilaEspera;
import JurisPro.modelo.Historico;



public class HistoricoDAO {
	
	public static HistoricoDAO singleton = null;
	
	public static HistoricoDAO getInstance() {
		if(singleton == null)
			singleton = new HistoricoDAO();
		return HistoricoDAO.singleton;
	}

	
	public boolean insertHistorico(Historico historico) {

		java.sql.Timestamp dataSql = new java.sql.Timestamp(historico.getDataEnvio().getTime());
		String sql = "INSERT INTO Historico (numProcesso, idDestinatario, idRemetente, dataEnvio, situacao) "
				+ "VALUES (?, ?, ?, ?, ?);";
				
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		boolean error = false;
		try {
			state.setString(1, historico.getNumProcesso());
			state.setInt(2, historico.getIdDestinatario());
			state.setInt(3, historico.getIdRemetente());
			state.setTimestamp(4, dataSql);
			state.setInt(5, 0);
			error = !state.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return error;
	}
	
	public int updateHistorico(Historico historico) {

	java.sql.Timestamp dataSql = new java.sql.Timestamp(historico.getDataEnvio().getTime());
	java.sql.Timestamp dataSql2 = new java.sql.Timestamp(historico.getDataResposta().getTime());
		
		String sql = "UPDATE Historico SET numProcesso = ?, IDDestinatario = ?, IDRemetente = ?, dataEnvio = ?, situacao = ?, " +
				"dataResposta = ? where IDRepasse = ? ;" ;
		PreparedStatement state = Conexao.getInstance().getPrepState(sql);
		
		int rows = 0;
		try {
			state.setString(1, historico.getNumProcesso());
			state.setInt(2, historico.getIdDestinatario());
			state.setInt(3, historico.getIdRemetente());
			state.setTimestamp(4, dataSql);
			state.setInt(5, historico.getSituacao());
			state.setTimestamp(6, dataSql2);
			state.setInt(7, historico.getIdRepasse());
			rows = state.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}										
		return rows;
	}

	public ArrayList<FilaEspera> selectProcessWaitingByDestine(int id){
		
		String sql = "SELECT h.IDRepasse, p.numProcesso, u.nome, u.tipo, p.autor, p.parte, p.cliente FROM ((Historico as h inner join Processo as p"+ 
		" ON h.numProcesso = p.numProcesso) inner join Usuario as u ON h.IDRemetente = u.IDUsuario)"+
		" WHERE h.situacao = 0 and h.IDDestinatario = ?";
		ArrayList<FilaEspera> dados = new ArrayList<FilaEspera>();	
		ResultSet rs = null;
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, id);
			rs = state.executeQuery();			
			
			FilaEspera fila = null;
			while (rs.next()){
				String cliente;
				if(rs.getBoolean("cliente")){
					cliente = rs.getString("autor");
				} else{
					cliente = rs.getString("parte");
				}
								 
				fila = new FilaEspera(rs.getInt("IDRepasse"), rs.getString("numProcesso"), cliente, rs.getString("nome"), rs.getInt("tipo"));				
				dados.add(fila);
				}
			rs.close();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dados;
	}
	
	public Historico selectHistoricoByIDRepasse(int IDRepasse){
		String sql = "SELECT numProcesso, idRemetente, idDestinatario, dataEnvio, situacao, dataResposta FROM Historico where IDRepasse = ? ;";
		Historico historico = null;
		ResultSet rs = null;
		try {
			PreparedStatement state = Conexao.getInstance().getPrepState(sql);
			state.setInt(1, IDRepasse);
			rs = state.executeQuery();
			Date data;
			while (rs.next()){
				historico = new Historico(IDRepasse, rs.getString("numProcesso"), rs.getInt("idRemetente"), rs.getInt("idDestinatario"));
				
				data = rs.getTimestamp("dataEnvio");
				historico.setDataEnvio(data);
				
				if(rs.getInt("situacao")!=0)
				if(rs.getDate("dataResposta")!=null){
					data = rs.getTimestamp("dataResposta");
					historico.setDataResposta(data);
				}
				
				historico.setSituacao(rs.getInt("situacao"));
				
			}
			rs.close();	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return historico;
	}
	
	public ArrayList<Historico> selectHistoricobyIdNumPro(String numPro){
		String sql = "SELECT idRepasse, idRemetente, idDestinatario, convert(varchar, dataEnvio, 103) as dataEnvio, dataEnvio,situacao, convert(varchar, dataResposta, 103) as dataResposta, dataResposta FROM Historico where numProcesso = "+numPro+" ORDER BY dataEnvio DESC;";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Historico historico = null;
		ArrayList<Historico> historicos = new ArrayList<Historico>();		
		try {
			while (rs.next()){
				// Pegando Historico do banco
				historico = new Historico(rs.getInt("idRepasse"), rs.getString("numProcesso") , rs.getInt("idRemetente"),
						 rs.getInt("idDestinatario"));
				historico.setSituacao(rs.getInt("situacao"));
				
				// Criando data de envio
				Date data = rs.getTimestamp("dataEnvio");
				historico.setDataEnvio(data);
				
				if(historico.getSituacao()!= 0){
					//Criando data de Resposta
					data = rs.getTimestamp("dataResposta");
					historico.setDataResposta(data);
				}
				historicos.add(historico);		
				
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return historicos;
	}
	
	
	
	public ArrayList<Historico> selectHistoricoAll(){
		String sql = "SELECT * FROM Agenda ORDER BY dataEnvio DESC";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Historico historico = null;
		ArrayList<Historico> historicos = new ArrayList<Historico>();
		
			
		try {
			while (rs.next()){
				historico = new Historico(rs.getInt("idRepasse"), rs.getString("numProcesso") , rs.getInt("idRemetente"),
						 rs.getInt("idDestinatario"));
				historico.setSituacao(rs.getInt("situacao"));
				
				// Criando data de envio
				Date data = rs.getTimestamp("dataEnvio");
				historico.setDataEnvio(data);
				if(historico.getSituacao()!= 0){
				// Criando data de Resposta
					data = rs.getTimestamp("dataResposta");
					historico.setDataResposta(data);
				}
				
				historicos.add(historico);				
			}
			rs.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return historicos;
	}
	
	public ArrayList<Historico> selectHistoricobyNumPro(String numPro){
		String sql = "SELECT h.NumProcesso, h.IDDestinatario, h.IDRemetente, (SELECT nome from Usuario WHERE IDUsuario = h.IDRemetente) as Remetente, "+ 
		"(SELECT nome from Usuario WHERE IDUsuario = h.IDDestinatario) as Destinatario, CONVERT(varchar, h.dataEnvio, 103) AS Envio," +
		"h.dataEnvio, CONVERT(varchar, h.dataResposta, 103) as Resposta, h. dataResposta, h.situacao " +
		"FROM Historico AS h INNER JOIN Usuario AS u ON h.IDDestinatario = u.IDUsuario " +
		"WHERE h.numProcesso = "+numPro+ " GROUP BY h.NumProcesso, u.nome, u.IDUsuario, h.dataEnvio, h.dataResposta, " +
		"h.situacao,h.IDRemetente, h.IDDestinatario ORDER BY dataEnvio DESC ;";
		ResultSet rs = Conexao.getInstance().selectComand(sql);
		Historico historico = null;
		ArrayList<Historico> historicos = new ArrayList<Historico>();		
		try {
			while (rs.next()){
				Date dataEn = rs.getTimestamp("dataEnvio");
				Date dataRes = rs.getTimestamp("dataResposta");
				historico = new Historico(rs.getString("numProcesso"), rs.getString("Remetente") , dataEn, 
						rs.getString("Destinatario"), dataRes, rs.getInt("situacao"));
				historico.setIdDestinatario(rs.getInt("IDDestinatario"));
				historico.setIdRemetente(rs.getInt("IDRemetente"));
				historicos.add(historico);	
				
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return historicos;
	}
	


}

	